import json

from flask import Flask, jsonify
import csv
import pymysql 
import csv

def read_csv(file_path):
    ids = []
    
    with open(file_path, 'r') as file:
        reader = csv.reader(file)
        next(reader)  # 跳过标题行
        
        for row in reader:
            ids.append(row[0])  # 假设ID在第一列，如果不是，请调整索引
        
    return ids

def build_query(ids):
    query = "SELECT * FROM b_thing WHERE id IN ({})".format(', '.join(ids))
    return query

def connect_to_database():
    connection = pymysql.connect(
        host="rm-cn-uqm3jz85n0009pno.rwlb.rds.aliyuncs.com",
        database="res",
        user="root",
        password="Dx123456"
    )
    
    return connection

def execute_query(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()
    connection.close()
    
    return result




csv_file = 'numbers_list.csv'

ids = read_csv(csv_file)

query = build_query(ids)

connection = connect_to_database()

result = execute_query(connection, query)

print(result)
# filtered_data = [item for item in result if item is not None]
#
#
#     # 将数据转换为字典或列表
# serialized_data = [{'id': item[0], 'title': item[1]} for sublist in filtered_data for item in sublist if item is not None and len(item) >= 2]  # 返回JSON格式的数据
#     #serialized_data = [{item: sublist[i] for i, item in enumerate(sublist)} for sublist in filtered_data if sublist]
#
# print(serialized_data)
# # if __name__ == '__main__':
# #     app.run(host='0.0.0.0', port=5000)

# 构建字典列表
data_list = []
for item in result:
    data_dict = {
        'id': item[0],
        'title': item[1],
        'nickname': item[2],
        'cover': item[3],
        'description': item[4],
        'price': item[5],
        'status': item[6],
        'create_time': item[7],
        'score': item[8],
        'pv': item[9],
        'recommend_count': item[10],
        'wish_count': item[11],
        'collect_count': item[12],
        'classification_id': item[13],
        'sex': item[14],
        'address': item[15]
    }
    data_list.append(data_dict)

# 转换为JSON字符串
json_data = json.dumps(data_list, ensure_ascii=False)
print(json_data)

with open('output.json', 'w', encoding='utf-8') as file:
    file.write(json_data)